import pandas as pd
import json
import re

""" operations = get_operations_from_excel(excel_path) 返回值示例
[
  {
    "operation_number": "1",
    "operation_name": "滚轮组件 ROLLER UNIT E160827-0101",
    "sub_operations": [
      {
        "sub_number": "1.1",
        "sub_name": "检查轴承，轴承孔和弹性挡圈使用低温油脂润滑 SKF LGLT2 Check bearing & bearing hole and circlip are lubricated with SKF LGLT2 grease."
      },
      {
        "sub_number": "1.2",
        "sub_name": "检查孔用弹性挡圈被正确安装 Check the circlips is fixed."
      },
      {
        "sub_number": "1.3",
        "sub_name": "检查滚轮支架头部压铆后要求与轴承垫片端部齐平 Check the head of roller support is flush with the end of bearing gasket after riveting."
      },
      {
        "sub_number": "1.4",
        "sub_name": "检查滚轮的旋转无阻力 Check the roller can rotate smoothly."
      }
    ]
  },
  {
    "operation_number": "2",
    "operation_name": "锁闭滚轮装配 LOCKING ROLLER ASSEMBLY E160858-0101",
    "sub_operations": [
      {
        "sub_number": "2.1",
        "sub_name": "检查轴套的安装和润滑油脂SKF LGLT2 Check axle sleeve is fixed with lubrication SKF LGLT2 该处滚轮安装后无需安装防尘盖 Don’t installation dust cover"
      }
    ]
  },
  {
    "operation_number": "3",
    "operation_name": "隔离锁Unlocking assembly FT0247328-101",
    "sub_operations": [
      {
        "sub_number": "3.1",
        "sub_name": "检查轴套，轴，扭簧和弹簧柱塞是否润滑情况，使用SKF LGLT2低温润滑油脂 Check axle sleeve, shaft, spring and push ball spring plunger are lubricated with SKF LGLT2."
      },
      {
        "sub_number": "3.2",
        "sub_name": "预安装开关，使用1mm垫片进行调整 Pre-assembly for switch, use 1mm shim to adjust. 快夹头和弹簧片勿漏装，勿丢失 No missing assembly for fastener and spring plate."
      },
      {
        "sub_number": "3.3",
        "sub_name": "检查旋转弹簧力扭力 Check the rotating spring rotation force 1.5Nm±0.5"
      },
      {
        "sub_number": "3.4",
        "sub_name": "增加PVC嵌条对线束进行保护 Add the PVC seal to protect the switch wiring. 检查标签信息正确 Check the label information for unlocking device."
      }
    ]
  },
  {
    "operation_number": "4",
    "operation_name": "上下部皮带张紧器Unlocking assembly E160830-101 E160831-101",
    "sub_operations": [
      {
        "sub_number": "4.1",
        "sub_name": "根据图纸和工艺文件要求，避免螺丝，垫片，压板漏装配 No missing screw/washer/belt tightener plate assembly"
      }
    ]
  },
  {
    "operation_number": "5",
    "operation_name": "拖链组件装配 Wiring chain assembly FT0246170-101",
    "sub_operations": [
      {
        "sub_number": "5.1",
        "sub_name": "检查线束在拖链内顺畅，未磨损，且固定良好 Check the wiring harness no block in chain, no damage and good fixation."
      }
    ]
  },
  {
    "operation_number": "6",
    "operation_name": "电机座装配 MOTOR BRACKET ASSEMBLY E161422-0105(with motor unit)/0103(without motor unit)",
    "sub_operations": [
      {
        "sub_number": "6.1",
        "sub_name": "检查电机座孔内卡簧无漏装，并已完全卡入孔槽内 Check circlip spring is fixed well into groove"
      },
      {
        "sub_number": "6.2",
        "sub_name": "检查轴承，隔环，齿轮和螺旋弹簧需要涂SKF LGLT2 油脂 Check bearing, spacer,gear,and helical springs are lubricated with SKF LGLT2 grease."
      },
      {
        "sub_number": "6.3",
        "sub_name": "检查电机轴末端的弹性挡圈装配良好 Check the circlip is fixed at the end of the motor shaft."
      },
      {
        "sub_number": "6.4",
        "sub_name": "螺旋扭簧安装时，使用0.5mm的垫片保证两侧安装后的间隙 When assemble spiral spring, use 0.5mm shim to ensure the clearance after installation on both sides"
      }
    ]
  },
  {
    "operation_number": "7",
    "operation_name": "移动主梁装配 Mobile beam assembly FT0246134-101",
    "sub_operations": [
      {
        "sub_number": "7.1",
        "sub_name": "检查所有钢珠是否存在 Check presence of all steel balls."
      },
      {
        "sub_number": "7.2",
        "sub_name": "检查C轨运行是否平滑，平移力小于等于10N check the sliding force of C rail should not greater than 10N"
      },
      {
        "sub_number": "7.3",
        "sub_name": "检查钢球罩,导轨是否涂低温油脂SKF LGLT2 Check ball cages,C-rail are lubricated by SKF LGLT2 grease"
      },
      {
        "sub_number": "7.4",
        "sub_name": "检查移动主梁铆接良好 Check the riveting well on mobile beam"
      }
    ]
  },
  {
    "operation_number": "8",
    "operation_name": "固定梁装配 Fixed BEAM GUIDE ASSEMBLY FT0246175-101",
    "sub_operations": [
      {
        "sub_number": "8.1",
        "sub_name": "检查导轨内的两侧拼接,两个面要求齐平,无明显台阶,允许小于0.3mm Check there is no obvious step or the step shall less than 0.3mm at beam guide junction area."
      },
      {
        "sub_number": "8.2",
        "sub_name": "增加PVC嵌条对线束进行保护 Add the PVC seal to protect the switch wiring."
      }
    ]
  },
  {
    "operation_number": "9",
    "operation_name": "电机皮带和同步皮带装配 Motor and Coordination Belt Assembly",
    "sub_operations": [
      {
        "sub_number": "9.1",
        "sub_name": "检查电机皮带和同步带上下部分的张紧力,用张力计测量皮带张紧力200N+/-10N The deflection on belt should be within 200N +/-10N by test at middle of one belt prependicularly with tensiometer."
      },
      {
        "sub_number": "9.2",
        "sub_name": "检查齿轮安装后的缺口/标记位置朝下，并且与同步带的皮带标志对齐(锁闭状态) Check the notch/mark position of gear is mounted downwards,and align with the belt logo of the sync belt(doors are under lock status)"
      },
      {
        "sub_number": "9.3",
        "sub_name": "确认皮带禁锢螺钉乐泰涂抹，压板未修饰，皮带未损坏。 Confirm the Lottice application on the screw, the plate no missing, the belt no damage."
      }
    ]
  },
  {
    "operation_number": "10",
    "operation_name": "锁紧轮同步调整 Lock Synchronization Adjustment",
    "sub_operations": [
      {
        "sub_number": "10.1",
        "sub_name": "检查右侧凸轮在完全锁闭时，左侧凸轮组件和止档有0~1mm间隙(下图红色) Check when the right cam is at lock position, the gap between left cam and rubber end stop has 0~1mm clearance."
      }
    ]
  },
  {
    "operation_number": "11",
    "operation_name": "",
    "sub_operations": [
      {
        "sub_number": "11.1",
        "sub_name": "检查导向座到主梁(C轨侧)打开和关闭尺寸差(塞拉行程70±1mm)，在锁轮6个螺丝紧固后对这个尺寸进行再次复测并确保其满足要求 Check the distance of sliding guide to main beam beween close and open status(plug stroke is 70±1/mm), re-validate and make sure this dimension is in tolerance after tightening the 6 bolts on locking roller."
      },
      {
        "sub_number": "11.2",
        "sub_name": "检查锁轮装配组件和凸轮之间间隙4.5±0.5mm Check the gap between each locking roller assembly and its locking cam."
      },
      {
        "sub_number": "11.3",
        "sub_name": "调整后，绑定不锈钢丝，注意绑定方向。 After adjustment, the stainless steel wire fixation and pay attention to the binding direction."
      }
    ]
  },
  {
    "operation_number": "12",
    "operation_name": "开关位置调整检查 Switch Adjusting Check",
    "sub_operations": [
      {
        "sub_number": "12.1",
        "sub_name": "放置2mm 垫片于橡胶止档和导向座之间，DLS能被激活 Put a 2mm shim between the rubber end stopper and locking roller,check the DLS is activated."
      },
      {
        "sub_number": "12.2",
        "sub_name": "开关摆臂轮处于极限压紧位置时，检查开关摆臂轮和凸轮的间隙距离＞6mm When switch arm roller is at maximum stroke position，check the gap shall over 6mm between DLS roller and cam"
      }
    ]
  },
  {
    "operation_number": "13",
    "operation_name": "电机线束固定位置检查 Motor Wiring Fixation Dimension Check",
    "sub_operations": [
      {
        "sub_number": "13.1",
        "sub_name": "电机线束固定位置检查 270±5mm (对应图纸塑料件位置22±5mm) The posititon of motor wiring plastic part 270±5mm. (Plastic part position 22±5mm)"
      },
      {
        "sub_number": "13.2",
        "sub_name": "接地线扎带固定，并检查塞拉开关门过程中，接线不会与金属面干涉 The earthing cable fixtation check, no interface with bracket surface when door open & close process."
      }
    ]
  },
  {
    "operation_number": "14",
    "operation_name": "门开度/行程检查 Door Opening Check",
    "sub_operations": [
      {
        "sub_number": "14.1",
        "sub_name": "检查关门状态下，门开度尺寸135±1mm Check door opening width in close status"
      },
      {
        "sub_number": "14.2",
        "sub_name": "调整梁的中心 A=B(+/-1mm) Adjust central for beam. A=B(+/-1mm)"
      },
      {
        "sub_number": "14.3",
        "sub_name": "检查开门状态下，门开度尺寸 669 (0,+2) Check door opening width in open status"
      },
      {
        "sub_number": "14.4",
        "sub_name": "锁门时，上驱动臂与上导轨产生不能干涉 There has no interference between upper driving arm with upper guide when door locking."
      }
    ]
  },
  {
    "operation_number": "15",
    "operation_name": "耐压测试 Dielectric Test",
    "sub_operations": [
      {
        "sub_number": "15.1",
        "sub_name": "耐压测试,分3次进行1/2/3介电测试 1) 110Vdc(750Vac/50Hz/10s) 2) 24/60Vdc(500Vac/50Hz/10s) 3) 5Vdc(500Vac/50Hz/10s)"
      }
    ]
  },
  {
    "operation_number": "16",
    "operation_name": "开关门功能检查 Door open & close functional Checks",
    "sub_operations": [
      {
        "sub_number": "16.1",
        "sub_name": "手动拉力 Manual force 检查手动开门所需要的力，平移<75N 塞拉<150N Check the force for manual opening operation Sliding force <75N，Plug force <150N"
      },
      {
        "sub_number": "16.2",
        "sub_name": "进行5个周期测试磨合和5个周期功能测试，检查电动开关门能正常工作 5 cycle lapping and 5 cycle test to check the door open & close function."
      },
      {
        "sub_number": "16.3",
        "sub_name": "检查LOS/EDS隔离开关信号功能 Isolation test for LOS/EDS function."
      }
    ]
  },
  {
    "operation_number": "17",
    "operation_name": "最终外观检查 Final Appearance Check",
    "sub_operations": [
      {
        "sub_number": "17.1",
        "sub_name": "接线组装和端子排 Cable Fixation& Terminal board 走线合理，安装牢固, 扎带没有尖角外露，端子排线号无缺失 Good wiring routing, fixed well and no sharp edge on collar, no missing number identification on terminal board"
      },
      {
        "sub_number": "17.2",
        "sub_name": "所有接地位置均要使用导电膏 Apply the grounding conductive paste on all grounding area."
      },
      {
        "sub_number": "17.3",
        "sub_name": "检查机构的扭矩记录，并且所有的紧固螺钉都有白色防松标识。 Check the torque record and white torque mark has been applied on fixed fastener"
      },
      {
        "sub_number": "17.4",
        "sub_name": "检查并记录机构标签信息零件号, 流水号, 生产日期和版本 Check and record the Operator label information with Part number, Serial number,Manufacture date, Rev."
      },
      {
        "sub_number": "17.5",
        "sub_name": "检查并记录隔离锁装配标签信息零件号, 流水号, 生产日期和版本 Check and record the Operator label information with Part number, Serial number,Manufacture date, Rev."
      },
      {
        "sub_number": "17.6",
        "sub_name": "检查并记录电机标签信息零件号, 流水号, 生产日期和版本 Check and record the Operator label information with Part number, Serial number,Manufacture date, Rev."
      },
      {
        "sub_number": "17.7",
        "sub_name": "检查并记录端子排线束标签信息 Check and record the terminal board wiring label information"
      }
    ]
  }
]
"""

def get_operations_from_excel(excel_path):
    """
    从Excel文件中读取工序步骤数据
    
    Args:
        excel_path (str): Excel文件路径
    
    Returns:
        list: 包含工序步骤的JSON列表
    """
    try:
        # 读取Excel文件的第一个工作表，使用with语句确保文件正确关闭
        with pd.ExcelFile(excel_path) as excel_file:
            sheet_name = excel_file.sheet_names[0]
            df = pd.read_excel(excel_file, sheet_name=sheet_name, header=None)
        
        # 搜索包含'操作 OPERATIONS'的单元格
        operations_row = None
        operations_col = None
        
        # 遍历所有单元格寻找包含'操作'和'OPERATIONS'的单元格
        for row in range(len(df)):
            for col in range(len(df.columns)):
                cell_value = str(df.iloc[row, col]) if pd.notna(df.iloc[row, col]) else ""
                # 去除所有空白字符后检查
                cleaned_value = re.sub(r'\s+', '', cell_value)
                
                if '操作' in cleaned_value and 'OPERATIONS' in cleaned_value.upper():
                    operations_row = row
                    operations_col = col
                    break
            if operations_row is not None:
                break
        
        if operations_row is None:
            return []
        
        return parse_operations_data(df, operations_row, operations_col)
        
    except Exception as e:
        return []

def parse_operations_data(df, start_row, col):
    """
    解析工序数据
    
    Args:
        df: DataFrame对象
        start_row: 开始行索引
        col: 列索引
    
    Returns:
        list: 工序步骤JSON列表
    """
    operations_list = []
    current_operation = None
    
    # 从找到的单元格下一行开始读取
    for i in range(start_row + 1, df.shape[0]):
        cell_value = str(df.iloc[i, col]).strip() if pd.notna(df.iloc[i, col]) else ""
        # 去除换行符，用空格替换
        cell_value = re.sub(r'[\r\n]+', ' ', cell_value).strip()
        
        if not cell_value:  # 跳过空单元格
            continue
            
        # 检查是否是主工序（以数字开头，如"1 滚轮组件"）
        main_operation_match = re.match(r'^(\d+)\s+(.+)', cell_value)
        if main_operation_match:
            # 如果之前有工序对象，先保存到列表中
            if current_operation:
                operations_list.append(current_operation)
            
            # 创建新的工序对象
            operation_number = main_operation_match.group(1)
            operation_name = re.sub(r'\s+', ' ', main_operation_match.group(2).strip())
            current_operation = {
                "operation_number": operation_number,
                "operation_name": operation_name,
                "sub_operations": []
            }
            continue
        
        # 检查是否是子工序（如1.1、1.2等）
        sub_operation_match = re.match(r'^(\d+\.\d+)\s*(.+)', cell_value)
        if sub_operation_match:
            sub_number = sub_operation_match.group(1)
            sub_name = re.sub(r'\s+', ' ', sub_operation_match.group(2).strip())
            
            # 提取主工序编号（如从"11.1"提取"11"）
            main_operation_number = sub_number.split('.')[0]
            
            # 如果当前没有工序对象，或者当前工序对象的编号与子工序的主编号不匹配
            if not current_operation or current_operation["operation_number"] != main_operation_number:
                # 如果之前有工序对象，先保存到列表中
                if current_operation:
                    operations_list.append(current_operation)
                
                # 创建新的工序对象，使用空字符串作为工序名称
                current_operation = {
                    "operation_number": main_operation_number,
                    "operation_name": "",
                    "sub_operations": []
                }
            
            # 添加子工序
            current_operation["sub_operations"].append({
                "sub_number": sub_number,
                "sub_name": sub_name
            })
    
    # 添加最后一个工序对象
    if current_operation:
        operations_list.append(current_operation)
    
    return operations_list
def main():
    """
    主函数，用于测试
    """
    # 这里需要指定Excel文件路径
    excel_path = "../frontend/QSD-1704401-303REV.A02 N05 Routine Test Procedure For Door Operator 例行检查程序 门机构 FT0246133-101T - 副本.xlsx"  # 使用实际存在的Excel文件
    
    operations = get_operations_from_excel(excel_path)
    
    # 打印整个JSON列表
    if operations:
        print(json.dumps(operations, ensure_ascii=False, indent=2))
    else:
        print("未找到工序步骤数据")
    
    return operations

if __name__ == "__main__":
    main()
